IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'checkmail')
CREATE USER [checkmail] FOR LOGIN [checkmail] WITH DEFAULT_SCHEMA=[dbo]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'anton')
CREATE USER [anton] FOR LOGIN [Anton] WITH DEFAULT_SCHEMA=[dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_Import_Stats_to_DailyStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE procedure [dbo].[up_Import_Stats_to_DailyStats]
as
begin
declare @date char(8)
declare @addrid bigint
declare @addr varchar(50)
declare @cur_dates cursor
declare @cur_emailaddresses cursor
set @cur_dates = cursor for
		select dbo.sys_Stats.date from dbo.sys_Stats group by dbo.sys_Stats.date
set @cur_emailaddresses = cursor for
	    select dbo.ut_Addressen.id from dbo.ut_Addressen
-- open the cursor
open @cur_dates
-- Fetch first line
FETCH NEXT FROM @cur_dates into @date
-- Loop the Fetches
WHILE(@@FETCH_STATUS=0)
BEGIN
	open @cur_emailaddresses

	FETCH NEXT FROM  @cur_emailaddresses into @addrid 
	WHILE (@@FETCH_STATUS=0)
	BEGIN
		--SELECT @addrid , @addr , @date
		INSERT INTO dbo.sys_DailyStats (
		date, emailid) VALUES (
		@date , @addrid )
		FETCH NEXT FROM @cur_emailaddresses	into @addrid
	END
	close @cur_emailaddresses
	FETCH NEXT FROM @cur_dates into @date
END
close @cur_dates



update dbo.sys_DailyStats set [count] = (SELECT COUNT(*) from dbo.sys_Stats WHERE dbo.sys_Stats.date=dbo.sys_DailyStats.date and dbo.sys_Stats.emailid=dbo.sys_DailyStats.emailid)
update dbo.sys_DailyStats set s_ok = (SELECT COUNT(*) from dbo.sys_Stats WHERE dbo.sys_Stats.date=dbo.sys_DailyStats.date and dbo.sys_Stats.emailid=dbo.sys_DailyStats.emailid and dbo.sys_Stats.status=1)
update dbo.sys_DailyStats set s_warn = (SELECT COUNT(*) from dbo.sys_Stats WHERE dbo.sys_Stats.date=dbo.sys_DailyStats.date and dbo.sys_Stats.emailid=dbo.sys_DailyStats.emailid and dbo.sys_Stats.status=2)
update dbo.sys_DailyStats set s_error = (SELECT COUNT(*) from dbo.sys_Stats WHERE dbo.sys_Stats.date=dbo.sys_DailyStats.date and dbo.sys_Stats.emailid=dbo.sys_DailyStats.emailid and dbo.sys_Stats.status=3)
update dbo.sys_DailyStats set s_unknown = (SELECT COUNT(*) from dbo.sys_Stats WHERE dbo.sys_Stats.date=dbo.sys_DailyStats.date and dbo.sys_Stats.emailid=dbo.sys_DailyStats.emailid and dbo.sys_Stats.status=4)
update dbo.sys_DailyStats set lagavg = (( s_ok * 100 + s_warn * 180 + s_error * 300  )+ 1) / ([count] + 1)

update dbo.sys_Stats set dbo.sys_Stats.lag = 100 where dbo.sys_Stats.Status = 1 and dbo.sys_Stats.lag=0
update dbo.sys_Stats set dbo.sys_Stats.lag = 180 where dbo.sys_Stats.Status = 2 and dbo.sys_Stats.lag=0
update dbo.sys_Stats set dbo.sys_Stats.lag = 300 where dbo.sys_Stats.Status = 3 and dbo.sys_Stats.lag=0
update dbo.sys_Stats set dbo.sys_Stats.lag = 0 where dbo.sys_Stats.Status = 4 and dbo.sys_Stats.lag=0

end



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_MigrationCMLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--truncate table dbo.Stats
--truncate table dailystats
--truncate table cmlog

CREATE proc [dbo].[up_MigrationCMLog] as


insert into dbo.sys_Stats (date,[timestamp],email,emailid,lag,status)
select replace(left(zeit,10),''-'','''')
	,cmlog.zeit
	,CMLog.email
	,ut_addressen.id
	,0
	,status1=
	case 
		when CMLog.status=''O'' then 1
		when CMLog.status=''C'' then 3
		when CMLog.status=''W'' then 2
		when CMLog.status=''n'' then 4
	end
from dbo.CMLog inner join ut_addressen on ut_addressen.email = cmlog.email
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_AppLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_AppLog](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[sys_timestamp] [datetime] NOT NULL CONSTRAINT [DF_sys_AppLog_sys_timestamp]  DEFAULT (getdate()),
	[emailid] [bigint] NULL,
	[email] [varchar](50) NULL,
	[logtext] [varchar](250) NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_AppLog]') AND name = N'IDX_AppLog_TimeStamp')
CREATE CLUSTERED INDEX [IDX_AppLog_TimeStamp] ON [dbo].[sys_AppLog] 
(
	[sys_timestamp] DESC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_Kontakte]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_Kontakte](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NULL CONSTRAINT [DF_Kontakte_beschreibung]  DEFAULT (''),
	[beschreibung] [varchar](250) NULL CONSTRAINT [DF_Kontakte_name]  DEFAULT (''),
	[email] [varchar](50) NULL CONSTRAINT [DF_Kontakte_email]  DEFAULT (''),
	[tel] [varchar](50) NULL CONSTRAINT [DF_Kontakte_tel]  DEFAULT (''),
	[aktiv] [bit] NOT NULL CONSTRAINT [DF_Kontakte_aktiv]  DEFAULT ((1)),
 CONSTRAINT [PK_Kontakte] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_Konfigs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_Konfigs](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NULL CONSTRAINT [DF__Konfigs__name__0F975522]  DEFAULT (''),
	[beschreibung] [varchar](250) NULL CONSTRAINT [DF__Konfigs__beschre__108B795B]  DEFAULT (''),
	[interval] [int] NULL CONSTRAINT [DF__Konfigs__interva__117F9D94]  DEFAULT ((300)),
	[warntime] [int] NULL CONSTRAINT [DF__Konfigs__warntim__1273C1CD]  DEFAULT ((180)),
	[crittime] [int] NULL CONSTRAINT [DF__Konfigs__crittim__1367E606]  DEFAULT ((240)),
	[alerttime] [int] NULL CONSTRAINT [DF__Konfigs__alertti__145C0A3F]  DEFAULT ((600)),
	[alert2time] [int] NULL,
	[notifyinterval] [int] NULL,
	[notify2interval] [int] NULL,
 CONSTRAINT [PK_Konfigs] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_DailyStats]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_DailyStats](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[date] [char](8) NULL,
	[emailid] [bigint] NULL,
	[count] [int] NULL,
	[lagavg] [int] NULL,
	[s_ok] [int] NULL,
	[s_warn] [int] NULL,
	[s_error] [int] NULL,
	[s_unknown] [int] NULL,
 CONSTRAINT [PK_DailyStats] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_Addressen]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_Addressen](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[addresse] [varchar](50) NULL,
	[email] [varchar](50) NULL,
	[beschreibung] [varchar](250) NULL,
	[aktiv] [bit] NULL CONSTRAINT [DF_Addressen_aktiv]  DEFAULT ((0)),
	[notify] [bit] NULL CONSTRAINT [DF_Addressen_notify]  DEFAULT ((0)),
	[status] [int] NULL CONSTRAINT [DF_Addressen_status]  DEFAULT ((0)),
	[time] [bigint] NULL,
	[konf] [bigint] NULL,
	[lastrun] [datetime] NULL CONSTRAINT [DF_ut_Addressen_lastrun]  DEFAULT (getdate()),
	[lastnotify] [datetime] NULL,
	[lastsuccess] [datetime] NULL CONSTRAINT [DF_ut_Addressen_lastsuccess]  DEFAULT (getdate()),
	[lag] [int] NULL,
	[IsNotified] [bit] NULL,
	[lastinit] [datetime] NULL,
 CONSTRAINT [PK_Addressen] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_Notify]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_Notify](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[addr_id] [bigint] NULL,
	[kontakt_id] [bigint] NULL,
	[mail] [bit] NULL,
	[mailtime_id] [bigint] NULL,
	[phone] [bit] NULL,
	[phonetime_id] [bigint] NULL,
 CONSTRAINT [PK_Notify] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Stats]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Stats](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[date] [char](8) NULL,
	[timestamp] [datetime] NOT NULL,
	[emailid] [bigint] NULL,
	[lag] [int] NULL,
	[status] [int] NULL,
 CONSTRAINT [PK_Stats] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_Stats]') AND name = N'IX_sys_Stats')
CREATE NONCLUSTERED INDEX [IX_sys_Stats] ON [dbo].[sys_Stats] 
(
	[emailid] ASC,
	[timestamp] ASC,
	[id] ASC
)
INCLUDE ( [lag],
[status]) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE TRIGGER [st_Create_DailyStats] 
   ON  [dbo].[sys_Stats] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for trigger here
    declare @today char(8)
	declare @emailid bigint
    declare @lagavg int
	declare @status int
	declare @s_ok int
	declare @s_warn int
	declare @s_error int
	declare @s_unknown int
	declare @id as int
	declare @timestamp as datetime
	declare @lag as int
declare allrow cursor for 
select * from inserted
open allrow 
fetch next from allrow into @ID,@today,@timestamp,@emailid,@lag,@status
while @@fetch_status=0
begin
    set @s_ok = 0
    set @s_warn = 0
    set @s_error = 0
	set @s_unknown = 0

	if @status = 1  set @s_ok = 1
	if @status = 2  set @s_warn = 1
	if @status = 3  set @s_error = 1
	if @status = 4  set @s_unknown = 1
    select @lagavg = avg(lag) from dbo.sys_Stats where dbo.sys_Stats.date = @today and emailid =  @emailid and lag is not null
    if exists (SELECT top 1 * FROM dbo.sys_DailyStats WHERE date=@today AND emailid = @emailid) 
        UPDATE dbo.sys_DailyStats 
		SET count = count + 1, 
		    lagavg = @lagavg,
			s_ok = s_ok  + @s_ok,
			s_warn = s_warn + @s_warn,
			s_error = s_error + @s_error,
			s_unknown = s_unknown + @s_unknown
		WHERE date=@today and emailid = @emailid;
    else
		INSERT INTO dbo.sys_DailyStats (emailid,count,date,s_ok,s_warn,s_error,s_unknown) VALUES (@emailid, 1,@today,@s_ok,@s_warn,@s_error,@s_unknown);
fetch next from allrow into @ID,@today,@timestamp,@emailid,@lag,@status

end
close allrow
deallocate allrow
END





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Weekdays]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Weekdays](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[weekday] [int] NULL,
	[weekdayname] [varchar](20) NULL,
 CONSTRAINT [PK_sys_Weekdays] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_TimeKonf]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_TimeKonf](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[konfid] [bigint] NULL,
	[konfname] [varchar](50) NULL,
	[weekday] [int] NULL,
	[starttime] [int] NULL,
	[stoptime] [int] NULL,
	[description] [varchar](max) NULL,
 CONSTRAINT [PK_ut_TimeKonf] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Checks]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Checks](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[addrid] [bigint] NULL,
	[started] [datetime] NULL,
	[mailuid] [char](36) NULL,
 CONSTRAINT [PK_Checks] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_GetDateByYearAndDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[func_GetDateByYearAndDay]
(
	-- Add the parameters for the function here
	@paramYear int,
	@paramDay int
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @loc_result datetime

	-- Add the T-SQL statements to compute the return value here
	select @loc_result = DATEADD(DAY, @paramDay, DATEADD(year, @paramYear - 1900, 0))

	-- Return the result of the function
	RETURN @loc_result

END
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_datetime_DateOnly]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'create  function [dbo].[sys_datetime_DateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_datetime_Date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [dbo].[sys_datetime_Date](@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
    begin
    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
    end
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_datetime_Time]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [dbo].[sys_datetime_Time](@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments). 
returns datetime
as
    begin
    return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
    end
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_datetime_TimeOnly]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [dbo].[sys_datetime_TimeOnly](@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
-- Thanks, Peso! 
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_datetime_DateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
create function [dbo].[sys_datetime_DateTime](@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
    begin
    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
    end
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_EnumDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create FUNCTION [dbo].[func_EnumDays]( 
	@param_date_from datetime,
	@param_date_to datetime)
	RETURNS @table_var TABLE
         (date_enum datetime)
AS
BEGIN
	DECLARE @curr_date datetime
	set @curr_date = @param_date_from

	WHILE (@curr_date <= @param_date_to) 
	BEGIN
      INSERT @table_var (date_enum) VALUES
        (@curr_date)
      SET @curr_date = DATEADD( DAY, 1, @curr_date)
    END

    RETURN
END    
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_getMailLag]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


CREATE FUNCTION [dbo].[func_getMailLag] 
(
	-- Add the parameters for the function here
	@mailid bigint
)
RETURNS int
AS
BEGIN
	-- decalre Variables und Cursors
	declare @newlag int
	set @newlag = 0
	declare @lastsuccess datetime
	declare @lastrun	datetime
	declare @lag		int
	declare @aktiv		bit
	SELECT  
		@lastsuccess =  dbo.ut_Addressen.lastsuccess, 
		@lastrun =		dbo.ut_Addressen.lastrun, 
		@aktiv =		dbo.ut_Addressen.aktiv,
		@lag =			dbo.ut_Addressen.lag
	FROM         
		dbo.ut_Addressen 
	WHERE 
		dbo.ut_Addressen.id = @mailid

	set	@newlag = NULL
	IF @aktiv = 1
	BEGIN
		IF @lastsuccess > @lastrun 
			set @newlag = datediff(second,@lastrun , @lastsuccess)
		else
			if @lastsuccess <= @lastrun and @lag < datediff(second,@lastrun , getdate())  
				set @newlag = datediff(second,@lastrun , getdate())
			else
				set @newlag = @lag
		
	END
    RETURN @newlag
END
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_isCheckTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


CREATE FUNCTION [dbo].[func_isCheckTime] 
(
	-- Add the parameters for the function here
	@timeid bigint
)
RETURNS int
AS
BEGIN
	-- decalre Variables und Cursors
	declare @minofday int
	declare @starttime int
	declare @stoptime int
	declare @res int
	set @res = 0
    select @minofday = DATEPART(hour, GETDATE()) * 60 + DATEPART(minute,GETDATE())
	declare alltimes cursor for	
		select starttime,stoptime from dbo.ut_TimeKonf where konfid = @timeid and weekday =  DATEPART(weekday,GETDATE())
	open alltimes 
	fetch next from alltimes into @starttime,@stoptime
	while @@fetch_status=0	
	begin
	    if @starttime < @minofday and @stoptime > @minofday set @res = 1
		fetch next from alltimes into @starttime,@stoptime
	end

    RETURN @res
END



' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_StatCache]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_StatCache](
	[timestamp] [datetime] NOT NULL,
	[emailid] [int] NOT NULL,
	[statusid] [int] NOT NULL,
	[valuecount] [int] NOT NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ut_StatCache]') AND name = N'IX_ut_stat_cache')
CREATE CLUSTERED INDEX [IX_ut_stat_cache] ON [dbo].[ut_StatCache] 
(
	[emailid] ASC,
	[timestamp] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_EnumMinutes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create FUNCTION [dbo].[func_EnumMinutes]( 
	@param_date datetime,
	@param_modulo int)
	RETURNS @table_var TABLE
         (timestamp_from datetime,
          timestamp_to datetime)
AS
BEGIN
	insert into @table_var(timestamp_from, timestamp_to)
	SELECT 
		DATEADD(n, sys_Minuten.Minuten, CAST( @param_date AS DATETIME)),
		DATEADD(n, sys_Minuten.Minuten+@param_modulo, CAST( @param_date AS DATETIME))
	FROM sys_Minuten 
	WHERE (sys_Minuten.Minuten % @param_modulo = 0)
		  and sys_Minuten.Minuten < 1440

    RETURN
END    
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_NotifyTemplates]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ut_NotifyTemplates](
	[IsSMS] [bit] NOT NULL,
	[PeriodNum] [smallint] NOT NULL,
	[Subject] [nvarchar](100) NULL,
	[Body] [nvarchar](max) NULL,
 CONSTRAINT [PK_ut_NotifyTemplates] PRIMARY KEY CLUSTERED 
(
	[IsSMS] ASC,
	[PeriodNum] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Status]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Status](
	[status] [int] NOT NULL,
	[text] [varchar](20) NULL,
 CONSTRAINT [PK_sys_Status] PRIMARY KEY CLUSTERED 
(
	[status] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_Status]') AND name = N'IX_sys_Status')
CREATE NONCLUSTERED INDEX [IX_sys_Status] ON [dbo].[sys_Status] 
(
	[status] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Tage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Tage](
	[Tage] [int] NOT NULL,
 CONSTRAINT [PK_sys_Tage] PRIMARY KEY CLUSTERED 
(
	[Tage] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_Tage]') AND name = N'IX_sys_Tage')
CREATE NONCLUSTERED INDEX [IX_sys_Tage] ON [dbo].[sys_Tage] 
(
	[Tage] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Minuten]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Minuten](
	[Minuten] [int] NOT NULL,
 CONSTRAINT [PK_sys_Minuten] PRIMARY KEY CLUSTERED 
(
	[Minuten] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_Minuten]') AND name = N'IX_sys_Minuten')
CREATE NONCLUSTERED INDEX [IX_sys_Minuten] ON [dbo].[sys_Minuten] 
(
	[Minuten] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_month]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_month](
	[month] [int] NOT NULL,
	[name] [varchar](50) NULL,
 CONSTRAINT [PK_sys_month] PRIMARY KEY CLUSTERED 
(
	[month] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[sys_month]') AND name = N'IX_sys_month')
CREATE NONCLUSTERED INDEX [IX_sys_month] ON [dbo].[sys_month] 
(
	[month] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_Weeks]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_Weeks](
	[Week] [int] NOT NULL,
 CONSTRAINT [PK_sys_Weeks] PRIMARY KEY CLUSTERED 
(
	[Week] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[sv_NotifyWho]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[sv_NotifyWho]
AS
SELECT     dbo.ut_Addressen.id, dbo.func_isCheckTime(dbo.ut_Notify.mailtime_id) AS domail, dbo.func_isCheckTime(dbo.ut_Notify.phonetime_id) AS dophone, 
                      dbo.ut_Notify.phone AS byphone, dbo.ut_Notify.mail AS bymail, dbo.ut_Kontakte.tel AS tophone, dbo.ut_Kontakte.email AS tomail, 
                      dbo.ut_Kontakte.name AS towho, dbo.ut_Addressen.email, dbo.ut_Addressen.lastsuccess
FROM         dbo.ut_Kontakte RIGHT OUTER JOIN
                      dbo.ut_Notify ON dbo.ut_Kontakte.id = dbo.ut_Notify.kontakt_id RIGHT OUTER JOIN
                      dbo.ut_Addressen ON dbo.ut_Notify.addr_id = dbo.ut_Addressen.id
WHERE     (dbo.func_isCheckTime(dbo.ut_Notify.mailtime_id) > 0) AND (dbo.ut_Kontakte.aktiv = 1) AND (dbo.ut_Addressen.aktiv = 1) OR
                      (dbo.ut_Kontakte.aktiv = 1) AND (dbo.func_isCheckTime(dbo.ut_Notify.phonetime_id) > 0) AND (dbo.ut_Addressen.aktiv = 1)
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[28] 2[13] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ut_Kontakte"
            Begin Extent = 
               Top = 130
               Left = 568
               Bottom = 277
               Right = 719
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ut_Notify"
            Begin Extent = 
               Top = 69
               Left = 299
               Bottom = 246
               Right = 450
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ut_Addressen"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 231
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 11
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 3330
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_NotifyWho'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_NotifyWho'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[sv_ChecksToRun]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[sv_ChecksToRun]
AS
SELECT     dbo.ut_Addressen.id, dbo.ut_Addressen.addresse, dbo.ut_Addressen.email, dbo.ut_Addressen.lastrun, dbo.ut_Konfigs.id AS konfid, 
                      dbo.ut_Addressen.status, dbo.ut_Addressen.time AS timeid
FROM         dbo.ut_Addressen LEFT OUTER JOIN
                      dbo.ut_Konfigs ON dbo.ut_Addressen.konf = dbo.ut_Konfigs.id
WHERE     (dbo.ut_Addressen.konf IS NOT NULL) AND (dbo.ut_Addressen.time IS NOT NULL) AND (DATEADD(second, dbo.ut_Konfigs.interval, 
                      dbo.ut_Addressen.lastrun) <= GETDATE()) AND (dbo.func_isCheckTime(dbo.ut_Addressen.time) > 0) AND (dbo.ut_Addressen.aktiv = 1)
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[25] 4[51] 2[7] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ut_Addressen"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 351
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ut_Konfigs"
            Begin Extent = 
               Top = 6
               Left = 328
               Bottom = 263
               Right = 479
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 28
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 5325
         Alias = 1290
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_ChecksToRun'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_ChecksToRun'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_getMailStatus]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'






CREATE FUNCTION [dbo].[func_getMailStatus] 
(
	-- Add the parameters for the function here
	@mailid bigint
)
RETURNS int
AS
BEGIN
	-- decalre Variables und Cursors
	declare @res int
	set @res = 0
	declare @lastsuccess datetime
	declare @lastrun	datetime
	declare @lastinit    datetime
	declare @warntime	int
	declare @crittime	int
	declare @aktiv		bit
	declare @addrtime	bigint
	declare @interval	int
	SELECT  
		@lastsuccess =  dbo.ut_Addressen.lastsuccess, 
		@lastrun  =		dbo.ut_Addressen.lastrun, 
		@lastinit =		dbo.ut_Addressen.lastinit,
		@warntime =		dbo.ut_Konfigs.warntime, 
		@crittime =		dbo.ut_Konfigs.crittime,
		@aktiv    =		dbo.ut_Addressen.aktiv,
		@addrtime =		dbo.ut_Addressen.[time],
		@interval =		dbo.ut_Konfigs.interval
	FROM         
		dbo.ut_Addressen LEFT OUTER JOIN
        dbo.ut_Konfigs 
	ON 
		dbo.ut_Addressen.konf = dbo.ut_Konfigs.id
	WHERE 
		dbo.ut_Addressen.id = @mailid
	
	IF @aktiv = 1 and dbo.func_isCheckTime(@addrtime) > 0
	BEGIN
		set @res = 1
		--IF (@lastsuccess < @lastrun) and (@lastsuccess is not NULL)
		--BEGIN
		--	IF getdate() > dateadd(second,@warntime ,@lastsuccess) set @res = 2
		--	IF getdate() > dateadd(second,@crittime ,@lastsuccess) set @res = 3
		--END 
		
		-- //++ changed by alex 2010-11-02
		if( @lastsuccess is not NULL )
		BEGIN
			IF @lastsuccess > dateadd(second,@warntime,@lastrun) set @res = 2 else set @res = 1
			IF @lastsuccess > dateadd(second,@crittime,@lastrun) set @res = 3 else set @res = 1
		END

		-- Time between init CheckMail
		if( (@lastsuccess is not NULL) AND (@res = 1))
		BEGIN
			IF getdate() > dateadd(second,@warntime+60/*Send-Recive Cycle*/,@lastsuccess) set @res = 2
			IF getdate() > dateadd(second,@crittime+60/*Send-Recive Cycle*/,@lastsuccess) set @res = 3
		END
		-- //-- changed by alex 2010-11-02

		
		if(@lastsuccess is NULL)
		BEGIN
			set @res = 4
		END
	END
	ELSE
		set	@res = 4
	
    RETURN @res
END




' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_isNotifyMail]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[func_isNotifyMail] 
(
	-- Add the parameters for the function here
	@mailid bigint
)
RETURNS int
AS
BEGIN
	-- decalre Variables und Cursors
	declare @res int
	set @res = 0
	declare @lastsuccess datetime
	declare @lastrun	 datetime
	declare @lastnotify	 datetime
	declare @lastinit    datetime
	declare @notifytime			int
	declare @notify2time		int
	declare @notifyinterval		int
	declare @notify2interval	int
	declare @interval	int
	declare @notify		bit
	declare @aktiv		bit
	declare @IsNotified	bit
	declare @addrtime	bigint
	SELECT  
		@lastsuccess =		dbo.ut_Addressen.lastsuccess, 
		@lastrun =			dbo.ut_Addressen.lastrun, 
		@lastnotify =		dbo.ut_Addressen.lastnotify,
		@lastinit   = 		dbo.ut_Addressen.lastinit,
		@notifytime  =		dbo.ut_Konfigs.alerttime, 
		@notify2time =		dbo.ut_Konfigs.alert2time, 
		@notifyinterval  =	dbo.ut_Konfigs.notifyinterval, 
		@notify2interval =	dbo.ut_Konfigs.notify2interval, 
		@interval =  		dbo.ut_Konfigs.interval, 
		@notify =			dbo.ut_Addressen.notify,
		@aktiv =			dbo.ut_Addressen.aktiv,
		@addrtime =			dbo.ut_Addressen.[time],
		@IsNotified =		dbo.ut_Addressen.[IsNotified]
	FROM         
		dbo.ut_Addressen LEFT OUTER JOIN
        dbo.ut_Konfigs 
	ON 
		dbo.ut_Addressen.konf = dbo.ut_Konfigs.id
	WHERE 
		dbo.ut_Addressen.id = @mailid
	
	
	
	IF @aktiv = 1 
	   AND 
-- (@lastnotify < @lastrun or @lastnotify is NULL) 
-- AND 
	   @notify = 1 
	   AND 
	   dbo.func_isCheckTime(@addrtime) > 0
	BEGIN

		-- time between start check(@lastrun) and close check(@lastsuccess) more than alerttime 
		IF @lastsuccess > dateadd(second,@notifytime,@lastrun) set @res = 1 else set @res = 0
		IF @lastsuccess > dateadd(second,@notify2time,@lastrun) set @res = 2 else set @res = 0

		-- time between start check(@lastrun) and current date more than alerttime 
		-- Time between init CheckMail
		if( @lastsuccess is not NULL)
		BEGIN
			IF getdate() > dateadd(second,@notifytime+60/*Send-Recive Cycle*/,@lastsuccess) set @res = 1
			IF getdate() > dateadd(second,@notify2time+60/*Send-Recive Cycle*/,@lastsuccess) set @res = 2
		END
		-- Time between last success 
		if( @lastsuccess is NULL)
		BEGIN
			IF getdate() > dateadd(second,@notifytime+60/*Send-Recive Cycle*/,@lastinit) set @res = 1
			IF getdate() > dateadd(second,@notify2time+60/*Send-Recive Cycle*/,@lastinit) set @res = 2
		END
		
	END
	ELSE
		set	@res = 0
		
	IF (@res = 0) AND (@IsNotified = 1)
	BEGIN
		set	@res = -1
	    -- UPDATE dbo.ut_Addressen SET IsNotified=0 WHERE dbo.ut_Addressen.id = @mailid
	END

	-- Check last notify in First/Second alarm period
	IF (@res = 1)
		IF getdate() < dateadd(second,@notifyinterval,@lastnotify) set @res = 0
	IF (@res = 2)
		IF getdate() < dateadd(second,@notify2interval,@lastnotify) set @res = 0
		
    RETURN @res
END





' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Notified]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'





-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.1.2009
-- Description:	Starts a new Check
-- =============================================
CREATE PROCEDURE [dbo].[sp_Notified] 
	-- Add the parameters for the stored procedure here
	@addrid bigint = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Variablen deklarieren
	declare @email varchar(50)
    -- Update LastCheck ind Address
    UPDATE dbo.ut_Addressen SET lastnotify = GETDATE(), IsNotified=1 WHERE dbo.ut_Addressen.id = @addrid
    -- get eMail Address
	SELECT @email = email FROM dbo.ut_Addressen WHERE dbo.ut_Addressen.id = @addrid
    -- Log this
    INSERT INTO dbo.sys_AppLog (emailid , email , logtext) VALUES ( @addrid , @email, ''Notify for this Address sent.'');
    
END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CloseCheck]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.01.2009
-- Description:	Closes a Check
-- =============================================
CREATE PROCEDURE [dbo].[sp_CloseCheck] 
	-- Add the parameters for the stored procedure here
	@mailuid char(36)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Declare needed Vars
    declare @addrid bigint
    declare @id bigint
    declare @started datetime
	declare @email varchar(50)
    declare @checkcount int
    declare @islastcheck int
    declare @lastcheckdate datetime
   
    -- Find the Check in the List
    SELECT @id = id , @addrid = addrid, @started=started from dbo.sys_Checks WHERE dbo.sys_Checks.mailuid = @mailuid
    
    --Last check
    SELECT @lastcheckdate = max(ch.started) from dbo.sys_Checks ch WHERE @addrid = ch.addrid  
    SELECT @islastcheck = case when datediff(second,ch.started,@lastcheckdate)<>0 then 0 else 1 end from dbo.sys_Checks ch WHERE @id = ch.id
    
    -- If we found an Addrid we remove it. We also need to add the Log entry and the Stat
    if (@addrid > 0) and (@islastcheck=1)
	begin
        -- First we remove the entry.. (Sure is sure)
		delete dbo.sys_Checks WHERE dbo.sys_Checks.id = @id  
        -- Get the eMail Address
		SELECT @email = email FROM dbo.ut_Addressen WHERE dbo.ut_Addressen.id = @addrid
		-- Log this
		INSERT INTO dbo.sys_AppLog (emailid , email , logtext) VALUES ( @addrid , @email, ''Check received - Closed - '' + @mailuid);
        -- If we receive a Check, the System works somehow. we set the Status to running, if this is the last open mail for this address
        -- Naughty.. we need some fixis here
		-- Anyhow eatem. We Set the Status
        -- Update Lastreceive etc
		UPDATE dbo.ut_Addressen SET lastsuccess = GETDATE(), lag = DATEDIFF(second, @started,GETDATE()) WHERE dbo.ut_Addressen.id = @addrid
		
    end
END







' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_StartCheck]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'





-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.1.2009
-- Description:	Starts a new Check
-- =============================================
CREATE PROCEDURE [dbo].[sp_StartCheck] 
	-- Add the parameters for the stored procedure here
	@addrid bigint = 0, 
	@mailuid char(36) = ''''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Variablen deklarieren
	declare @email varchar(50)
    -- Insert the new Check into the Checklist
    INSERT INTO dbo.sys_Checks (addrid , mailuid , started ) VALUES (@addrid , @mailuid , GETDATE())
    -- Update LastCheck ind Address
    UPDATE dbo.ut_Addressen SET lastrun = GETDATE() WHERE dbo.ut_Addressen.id = @addrid
    -- get eMail Address
	SELECT @email = email FROM dbo.ut_Addressen WHERE dbo.ut_Addressen.id = @addrid
    -- Log this
    INSERT INTO dbo.sys_AppLog (emailid , email , logtext) VALUES ( @addrid , @email, ''New Check Added'');
    
END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_InitCheckMail]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'



-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.01.2009
-- Description:	Cleanup on Startup
-- =============================================
CREATE PROCEDURE [dbo].[sp_InitCheckMail] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Remove all Old Checkmails
	TRUNCATE TABLE dbo.sys_Checks 
	-- Update Addressen, make sure we start over now.
	-- Even more Status is on 4 (Unknown)
	UPDATE dbo.ut_Addressen 
	SET 
		dbo.ut_Addressen.Status = 4 ,
		dbo.ut_Addressen.lastrun = GETDATE() , 
		dbo.ut_Addressen.lastinit = GETDATE() , 
		dbo.ut_Addressen.lastsuccess = null,
		dbo.ut_Addressen.IsNotified=''false''
	-- Funky - we should add some log on Init
	INSERT INTO dbo.sys_AppLog (email,logtext) VALUES (''[SYSTEM]'',''Init - Cleanup in DB running'')
END




' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[func_stat_GetDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'

-- =============================================
-- Author:		alexandr.pec@skypro.ch
-- Create date: 2010-03-22
-- Description:	Get day statistic 
-- =============================================
CREATE FUNCTION [dbo].[func_stat_GetDay]( 
	@param_emailid int,
	@param_date datetime,
	@param_get_lag bit)
RETURNS @table_var TABLE(
	timestamp datetime,
	statusid int,
	valuecount int,
	lag int)
AS
BEGIN

	-- for cursor	
	DECLARE @cur_timestamp datetime
	DECLARE @cur_emailid int
	DECLARE @cur_lag int
	DECLARE @cur_status int
	DECLARE @cur_valuecount int
	DECLARE @cur_last_status int
	DECLARE @cur_last_lag int
	
	DECLARE @avg_lag int
	SELECT @avg_lag = AVG(sys_Stats.lag)
	FROM sys_Stats 
	WHERE (sys_Stats.emailid = @param_emailid) 
	  --AND (sys_Stats.date = @param_date)
	  AND (sys_Stats.timestamp >= @param_date)
	  AND (sys_Stats.timestamp <= DATEADD(DAY,1,@param_date))

	if @param_get_lag = ''true''
	-- Get data from sys_stats with "lag" value
	BEGIN
		DECLARE groups_cursor CURSOR FOR
		SELECT        
			coalesce( sys_Stats_1.timestamp,
					[dbo].[func_EnumMinutes].timestamp_from
				) AS ''timestamp'',	
			coalesce(@param_emailid, 0) AS emailid,
			coalesce(sys_Stats_1.lag, @avg_lag, 0) AS lag2, 
			coalesce(sys_Stats_1.status, 4) AS status2
		FROM [dbo].[func_EnumMinutes](@param_date,5)
		LEFT OUTER JOIN [dbo].[sys_Stats] AS sys_Stats_1 ON 
			(sys_Stats_1.emailid = @param_emailid) 
			AND (
			(sys_Stats_1.timestamp >= [dbo].[func_EnumMinutes].timestamp_from )
			AND
			(sys_Stats_1.timestamp < [dbo].[func_EnumMinutes].timestamp_to ) )
		ORDER BY [dbo].[func_EnumMinutes].timestamp_from 
		
		set @cur_last_status = -1
		set @cur_last_lag = -1
		set @cur_valuecount = 0
		
		open groups_cursor
		while @@fetch_status = 0
	 	begin
			fetch next from groups_cursor 
			into @cur_timestamp, @cur_emailid, @cur_lag, @cur_status
			
			if (@cur_status <> @cur_last_status)
			OR (@cur_lag <> @cur_last_lag)
			OR (@@fetch_status <> 0)
			BEGIN
				if @cur_last_status >=0
				BEGIN
		 			insert into @table_var
		 			values(@cur_timestamp, @cur_last_status, @cur_valuecount, @cur_last_lag)
				END 
				
				set @cur_last_status = @cur_status
				set @cur_last_lag = @cur_lag
				set @cur_valuecount = 0
			END 
			
			set @cur_valuecount = @cur_valuecount + 1
		end
		close groups_cursor
	 	deallocate groups_cursor
	
	END
	else
	-- Get data from ut_StatCache without "lag" value
	BEGIN 
		DECLARE groups_cursor_no_lag CURSOR FOR
		SELECT        
			coalesce( sys_Stats_1.timestamp,
					[dbo].[func_EnumMinutes].timestamp_from
				) AS ''timestamp'',	
			coalesce(@param_emailid, 0) AS emailid,
			coalesce(@avg_lag, 0) AS lag2, 
			coalesce(sys_Stats_1.status, 4) AS status2
		FROM [dbo].[func_EnumMinutes](@param_date,5)
		LEFT OUTER JOIN [dbo].[sys_Stats] AS sys_Stats_1 ON 
			(sys_Stats_1.emailid = @param_emailid) 
			AND (
			(sys_Stats_1.timestamp >= [dbo].[func_EnumMinutes].timestamp_from )
			AND
			(sys_Stats_1.timestamp < [dbo].[func_EnumMinutes].timestamp_to ) )
		ORDER BY [dbo].[func_EnumMinutes].timestamp_from 
		
		set @cur_last_status = -1
		--set @cur_last_lag = -1
		set @cur_valuecount = 0
		
		open groups_cursor_no_lag
		while @@fetch_status = 0
	 	begin
			fetch next from groups_cursor_no_lag 
			into @cur_timestamp, @cur_emailid, @cur_lag, @cur_status
			
			if (@cur_status <> @cur_last_status)
			--OR (abs(@cur_lag - @cur_last_lag)>5)
			OR (@@fetch_status <> 0)
			BEGIN
				if @cur_last_status >=0
				BEGIN
		 			insert into @table_var
		 			values(@cur_timestamp, @cur_last_status, @cur_valuecount, 0)
				END 
				
				set @cur_last_status = @cur_status
				--set @cur_last_lag = @cur_lag
				set @cur_valuecount = 0
			END 
			
			set @cur_valuecount = @cur_valuecount + 1
		end
		close groups_cursor_no_lag
	 	deallocate groups_cursor_no_lag
	 	
	END
	
	RETURN 
END


' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[uv_MailStatus]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[uv_MailStatus]
AS
SELECT     dbo.ut_Addressen.id, dbo.ut_Addressen.email, dbo.ut_Addressen.lastsuccess, dbo.ut_Addressen.lastrun, dbo.ut_Konfigs.warntime, 
                      dbo.ut_Konfigs.crittime, dbo.func_getMailStatus(dbo.ut_Addressen.id) AS status, dbo.ut_Addressen.aktiv, dbo.ut_Addressen.notify, 
                      dbo.func_getMailLag(dbo.ut_Addressen.id) AS lag
FROM         dbo.ut_Addressen LEFT OUTER JOIN
                      dbo.ut_Konfigs ON dbo.ut_Addressen.konf = dbo.ut_Konfigs.id
WHERE     (dbo.ut_Addressen.time IS NOT NULL) AND (dbo.ut_Addressen.konf IS NOT NULL)
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[46] 4[24] 2[6] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ut_Konfigs"
            Begin Extent = 
               Top = 11
               Left = 216
               Bottom = 277
               Right = 367
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ut_Addressen"
            Begin Extent = 
               Top = 14
               Left = 21
               Bottom = 268
               Right = 172
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 11
         Width = 284
         Width = 1500
         Width = 3480
         Width = 3045
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 3510
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'uv_MailStatus'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'uv_MailStatus'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DoStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'





-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.01.2009
-- Description:	Do the Stats Trick
-- =============================================
CREATE PROCEDURE [dbo].[sp_DoStats] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Define the Variables
	declare @cur_addresses cursor
	declare @addr_id bigint
	declare @addr_status int
	declare @addr_lag int
	declare @addr_time bigint
	-- Make a Cursor for all Aktiv Addresses (Enabled only) including Out of Time.. The get Greycount
	set @cur_addresses = cursor for
			SELECT dbo.ut_Addressen.id, dbo.func_getMailStatus(dbo.ut_Addressen.id), dbo.func_getMailLag(dbo.ut_Addressen.id), dbo.ut_Addressen.[time] FROM dbo.ut_Addressen
            WHERE (dbo.ut_Addressen.konf IS NOT NULL) AND (dbo.ut_Addressen.[time] IS NOT NULL) AND (dbo.ut_Addressen.aktiv = 1) 
	-- open the cursor
	open @cur_addresses
    -- Fetch first line
	FETCH NEXT FROM @cur_addresses into @addr_id,@addr_status,@addr_lag,@addr_time
    -- Loop the Fetches
	WHILE(@@FETCH_STATUS=0)
	BEGIN
		-- Add Stats ( akt or grey.. )
		INSERT INTO dbo.sys_Stats (date , emailid , lag, status,[timestamp]) VALUES (CONVERT(char(8),GETDATE(),112) , @addr_id , @addr_lag,@addr_status,getdate()) 
		FETCH NEXT FROM @cur_addresses into @addr_id,@addr_status,@addr_lag,@addr_time
	END


END





' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DoLagAndStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






-- =============================================
-- Author:		Thomas Bucher
-- Create date: 31.01.2009
-- Description:	Do the Stats Trick
-- =============================================
CREATE PROCEDURE [dbo].[sp_DoLagAndStatus] 
AS
BEGIN
	UPDATE dbo.ut_Addressen 
	SET dbo.ut_Addressen.status = dbo.func_getMailStatus(dbo.ut_Addressen.id), 
		dbo.ut_Addressen.lag = dbo.func_getMailLag(dbo.ut_Addressen.id);
END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[sv_ChecksToNotify]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[sv_ChecksToNotify]
AS
SELECT     dbo.ut_Addressen.id, dbo.ut_Addressen.email, dbo.ut_Addressen.lastsuccess, dbo.ut_Addressen.lastrun, dbo.ut_Konfigs.alerttime, 
                      dbo.ut_Addressen.lastnotify, dbo.func_isNotifyMail(dbo.ut_Addressen.id) AS ''notify_res''
FROM         dbo.ut_Addressen LEFT OUTER JOIN
                      dbo.ut_Konfigs ON dbo.ut_Addressen.konf = dbo.ut_Konfigs.id
WHERE     (dbo.ut_Addressen.time IS NOT NULL) AND (dbo.ut_Addressen.konf IS NOT NULL) AND (dbo.func_isNotifyMail(dbo.ut_Addressen.id) <> 0) AND 
                      (dbo.ut_Addressen.notify = 1)
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ut_Addressen"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 277
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ut_Konfigs"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 274
               Right = 378
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 11
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_ChecksToNotify'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_ChecksToNotify'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_stat_GetPeriod]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		alexandr.pec@skypro.ch
-- Create date: 2010-03-22
-- Description:	Get statistic
-- =============================================
CREATE PROCEDURE  [dbo].[sp_stat_GetPeriod] 
	@param_date_from datetime,
	@param_date_to datetime,
	@param_addressid int,
	@param_show_lag bit 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
	
	-- normalize input params
	--@param_date_from = CONVERT(DATETIME, @param_date_from, 101)
	--@param_date_to = CONVERT(DATETIME, @param_date_to, 101)
	if( @param_addressid is NULL)
		set @param_addressid = 0
		
	IF( NOT exists (select * from tempdb.dbo.sysobjects where name=''#TmpResultTable'' ))
	BEGIN
		CREATE TABLE #TmpResultTable(
			[timestamp] datetime,
			[emailid] bigint,
			[email] varchar(50),
			[statusid] int,
			[text] varchar(20),
			[valuecount] int,
			[lag] int
			)
	END
	DELETE FROM #TmpResultTable
		
    --Period query
    DECLARE period_address_cursor CURSOR SCROLL FOR
	SELECT 
		[dbo].[func_EnumDays].date_enum,
		[dbo].[ut_Addressen].id,
		[dbo].[ut_Addressen].email		
	FROM [dbo].[func_EnumDays] (@param_date_from, @param_date_to)
	LEFT JOIN [dbo].[ut_Addressen] ON (@param_addressid=0) 
								   or ([dbo].[ut_Addressen].id = @param_addressid)

	DECLARE @date_enum datetime
	DECLARE @emailid int
	DECLARE @email varchar(50);
	set @date_enum = null;
	 
    open period_address_cursor
	while((@@fetch_status = 0) or (@date_enum is NULL)) 
 	begin
 	  
 		if( @date_enum is NULL)   -- first record
			fetch first from period_address_cursor 
				into @date_enum, @emailid, @email
 		else
			fetch next from period_address_cursor 
				into @date_enum, @emailid, @email
		
--		SELECT @date_enum, @emailid, @email
		
		IF( (@param_show_lag = ''true'') 
		    OR
		    ( @date_enum >= DATEADD(DAY,-1,GETDATE()) ))
		BEGIN
		
			-- if show lag, then no use cache
			insert into #TmpResultTable(
				[timestamp], 
				[emailid],
				[email],
				[statusid],
				[text],
				[valuecount],
				[lag]
				)
			SELECT 
				[dbo].[func_stat_GetDay].timestamp,
				(@emailid) as emailid,
				(@email) as email,
				[dbo].[func_stat_GetDay].statusid,
				[dbo].[sys_Status].text,
				[dbo].[func_stat_GetDay].valuecount,
				CASE when (@param_show_lag = ''true'') then [dbo].[func_stat_GetDay].lag else 0 END AS lag
			FROM [dbo].[func_stat_GetDay](@emailid, @date_enum, ''true'') 
			LEFT OUTER JOIN [dbo].[sys_Status] ON [dbo].[sys_Status].status = [dbo].[func_stat_GetDay].statusid
		END
		else
		BEGIN
--			 SELECT 1;
		   
			-- get data and save to cache
			
			if( NOT EXISTS(SELECT * FROM [dbo].[ut_StatCache] 
					   WHERE ( [dbo].[ut_StatCache].emailid =  @emailid)
					     and ( [dbo].[ut_StatCache].[timestamp] >= @date_enum)
						 and ( [dbo].[ut_StatCache].[timestamp] <  DATEADD( DAY, 1, @date_enum) )  ) )
			BEGIN
				--begin transaction
				INSERT INTO [checkmail].[dbo].[ut_StatCache]
					   ([timestamp]
					   ,[emailid]
					   ,[statusid]
					   ,[valuecount])
				SELECT 
					[dbo].[func_stat_GetDay].timestamp,
					(@emailid) as emailid,
					[dbo].[func_stat_GetDay].statusid,
					[dbo].[func_stat_GetDay].valuecount
				FROM [dbo].[func_stat_GetDay](@emailid, @date_enum, ''false'') 
				LEFT OUTER JOIN [dbo].[sys_Status] ON [dbo].[sys_Status].status = [dbo].[func_stat_GetDay].statusid
				--commit
				
			END
			
			-- read data from cache
			insert into #TmpResultTable(
				[timestamp], 
				[emailid],
				[email],
				[statusid],
				[text],
				[valuecount],
				[lag]
				)
			SELECT 
				[dbo].[ut_StatCache].timestamp,
				[dbo].[ut_StatCache].emailid,
				(@email) as email,
				[dbo].[ut_StatCache].statusid,
				[dbo].[sys_Status].text,
				[dbo].[ut_StatCache].valuecount,
				cast( 0  as int) lag
			FROM [dbo].[ut_StatCache] 
			LEFT OUTER JOIN [dbo].[sys_Status] ON [dbo].[sys_Status].status = [dbo].[ut_StatCache].statusid
			WHERE ( [dbo].[ut_StatCache].emailid =  @emailid ) 
			  and (( [dbo].[ut_StatCache].[timestamp] >= @date_enum)
 			  and  ( [dbo].[ut_StatCache].[timestamp] <  DATEADD( DAY, 1, @date_enum) ) )
		END
		
		
	end
	close period_address_cursor
 	deallocate period_address_cursor	
 	
 	SELECT * FROM #TmpResultTable
 	ORDER BY [emailid], [timestamp]
    
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[sv_NotifyToRun]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[sv_NotifyToRun]
AS
SELECT     dbo.sv_ChecksToNotify.id, dbo.sv_ChecksToNotify.email, dbo.sv_ChecksToNotify.lastsuccess, dbo.sv_ChecksToNotify.lastrun, 
                      dbo.sv_ChecksToNotify.lastnotify, dbo.sv_NotifyWho.domail, dbo.sv_NotifyWho.dophone, dbo.sv_NotifyWho.byphone, dbo.sv_NotifyWho.bymail, 
                      dbo.sv_NotifyWho.tophone, dbo.sv_NotifyWho.tomail, dbo.sv_NotifyWho.towho, dbo.sv_ChecksToNotify.notify_res
FROM         dbo.sv_ChecksToNotify LEFT OUTER JOIN
                      dbo.sv_NotifyWho ON dbo.sv_ChecksToNotify.id = dbo.sv_NotifyWho.id
WHERE     (dbo.sv_NotifyWho.id IS NOT NULL)
' 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "sv_ChecksToNotify"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 198
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "sv_NotifyWho"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 241
               Right = 378
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 17
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_NotifyToRun'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'sv_NotifyToRun'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_stat_GetMonth]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		alexandr.pec@skypro.ch
-- Create date: 2010-03-22
-- Description:	Get statistic for month
-- =============================================
CREATE PROCEDURE  [dbo].[sp_stat_GetMonth] 
	@param_year int,
	@param_month int,
	@param_addressid int,
	@param_show_lag bit 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	DECLARE	@loc_date_from datetime
	DECLARE	@loc_date_to datetime
	
	SELECT 
		@loc_date_from = min(date_enum), -- from monday to sunday
		@loc_date_to = max(date_enum)    -- from monday to sunday
	FROM [dbo].[func_EnumDays](
		[dbo].[func_GetDateByYearAndDay](@param_year, 0),
		DATEADD(YEAR,1,[dbo].[func_GetDateByYearAndDay](@param_year, 0))
		)
	WHERE DATEPART(MONTH,[dbo].[func_EnumDays].date_enum)=@param_month		
	  and DATEPART(YEAR,[dbo].[func_EnumDays].date_enum)=@param_year

	EXEC [dbo].[sp_stat_GetPeriod]
		@param_date_from = @loc_date_from,
		@param_date_to = @loc_date_to,
		@param_addressid = @param_addressid,
		@param_show_lag = @param_show_lag
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_stat_GetWeek]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:		alexandr.pec@skypro.ch
-- Create date: 2010-03-22
-- Description:	Get statistic for month
-- =============================================
CREATE PROCEDURE  [dbo].[sp_stat_GetWeek] 
	@param_year int,
	@param_week int,
	@param_addressid int,
	@param_show_lag bit 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	DECLARE	@loc_date_from datetime
	DECLARE	@loc_date_to datetime
	
	SELECT 
		@loc_date_from = min(DATEADD(DAY,1,date_enum)), -- from monday to sunday
		@loc_date_to = max(DATEADD(DAY,1,date_enum))    -- from monday to sunday
	FROM [dbo].[func_EnumDays](
		[dbo].[func_GetDateByYearAndDay](@param_year, 0),
		DATEADD(YEAR,1,[dbo].[func_GetDateByYearAndDay](@param_year, 0))
		)
	WHERE DATEPART(ww,[dbo].[func_EnumDays].date_enum)=@param_week		
	  and DATEPART(YEAR,[dbo].[func_EnumDays].date_enum)=@param_year

	EXEC [dbo].[sp_stat_GetPeriod]
		@param_date_from = @loc_date_from,
		@param_date_to = @loc_date_to,
		@param_addressid = @param_addressid,
		@param_show_lag = @param_show_lag
END

' 
END
